drop view report_contract_payment;

-- попробуй сделать это, если не прокатит то удали и добавь поле amount(15,2)
ALTER TABLE magfin.public.contract_add_agree ALTER COLUMN amount TYPE NUMERIC(15,2)  USING amount::numeric;

-- создай вьюху 
CREATE VIEW
    x_contracts
    (
        id,
        contract_date,
        contract_d,
        contractat_flg,
        contract_no,
        ref_company_fk,
        company,
        finalizat_flg,
        finalizat,
        amount,
        prepayment,
        datorie,
        consultant_id,
        consultant,
        consultant_amount,
        personal_id,
        personal,
        personal_amount,
        personal2_id,
        personal2,
        personal2_amount
    ) AS
SELECT
    c.id,
    TO_CHAR(c.contract_date, 'DD.MM.YYYY'::text) AS contract_date,
    c.contract_date                              AS contract_d,
    c.contractat_flg,
    CASE
        WHEN (c.contractat_flg = (1)::NUMERIC)
        THEN (c.contract_no)::text
        ELSE ''::text
    END AS contract_no,
    c.ref_company_fk,
    (
        SELECT
            comp.md_value
        FROM
            ref_company comp
        WHERE
            (
                comp.id = c.ref_company_fk)) AS company,
    c.finalizat_flg,
    CASE
        WHEN (c.finalizat_flg = (1)::NUMERIC)
        THEN (c.contract_no)::text
        ELSE ''::text
    END                                          AS finalizat,
    COALESCE(c.amount, 0.00)                          AS amount,
    COALESCE(c.prepayment, 0.00)                      AS prepayment,
    COALESCE((c.amount - c.prepayment), 0.00)         AS datorie,
    c.respons_consultant_fk                           AS consultant_id,
    "get_sec_user_FIO_by_id"(c.respons_consultant_fk) AS consultant,
    ROUND(((COALESCE(c.amount, 0.00) / (100)::NUMERIC) * get_sec_user_service_procent_by_id
    (c.respons_consultant_fk, c.ref_services_fk)), 2) AS consultant_amount,
    c.respons_personal_fk                             AS personal_id,
    "get_sec_user_FIO_by_id"(c.respons_personal_fk)   AS personal,
    ROUND(((COALESCE(c.amount, 0.00) / (100)::NUMERIC) * get_sec_user_service_procent_by_id
    (c.respons_personal_fk, c.ref_services_fk)), 2)  AS personal_amount,
    c.respons_personal2_fk                           AS personal2_id,
    "get_sec_user_FIO_by_id"(c.respons_personal2_fk) AS personal2,
    ROUND(((COALESCE(c.amount, 0.00) / (100)::NUMERIC) * get_sec_user_service_procent_by_id
    (c.respons_personal2_fk, c.ref_services_fk)), 2) AS personal2_amount
FROM
    contracts c
ORDER BY
    c.id;
    
ALTER TABLE x_contracts
  OWNER TO mf;
  
  
-- Возврашает процент по партнеру
CREATE OR REPLACE FUNCTION "public"."get_reg_partners_procent_by_id" (regpartner_id numeric)  RETURNS numeric
  VOLATILE
AS $dbvis$
DECLARE
   result numeric;
 BEGIN
    if regpartner_id is null then  
        result = 0; 
        RETURN result; 
    end if;
 
    select procent into result from reg_partners where id  = regpartner_id;
    
    if result is null then  
        result = 0;  
    end if;
    
  RETURN result;
END;
$dbvis$ LANGUAGE plpgsql

  
-- Теперь создаем главную ВЬЮХУ
    
DROP VIEW "public"."vreport_contract_payments";

create view vreport_contract_payments as 
select un.*, 
        cp.amount as payd_amount, 
        cp.date_trans as payment_date
from 
(
        select  gr.id,
                gr.contract_date,
                gr.contract_d,
                gr.contractat_flg,
                gr.contract_no,
                gr.ref_company_fk,
                gr.company,
                gr.finalizat_flg,
                gr.finalizat,
                gr.amount,
                gr.prepayment,
                gr.datorie,
                gr.uid,
                gr.uname,
                sum(gr.uamount)
         from (
                select 
                        xc.id,
                        xc.contract_date,
                        xc.contract_d,
                        xc.contractat_flg,
                        xc.contract_no,
                        xc.ref_company_fk,
                        xc.company,
                        xc.finalizat_flg,
                        xc.finalizat,
                        xc.amount,
                        xc.prepayment,
                        xc.datorie,
                        xc.consultant_id        as uid,
                        xc.consultant           as uname,
                        xc.consultant_amount    as uamount
                  from x_contracts xc
                  where consultant_id is not null
                  union all
                  select 
                        xc.id,
                        xc.contract_date,
                        xc.contract_d,
                        xc.contractat_flg,
                        xc.contract_no,
                        xc.ref_company_fk,
                        xc.company,
                        xc.finalizat_flg,
                        xc.finalizat,
                        xc.amount,
                        xc.prepayment,
                        xc.datorie,
                        xc.personal_id         as uid,
                        xc.personal             as uname,
                        xc.personal_amount      as uamount
                  from x_contracts xc
                  where personal_id is not null
                    union all
                  select 
                        xc.id,
                        xc.contract_date,
                        xc.contract_d,
                        xc.contractat_flg,
                        xc.contract_no,
                        xc.ref_company_fk,
                        xc.company,
                        xc.finalizat_flg,
                        xc.finalizat,
                        xc.amount,
                        xc.prepayment,
                        xc.datorie,
                        xc.personal2_id         as uid,
                        xc.personal2            as uname,
                        xc.personal2_amount     as uamount
                  from x_contracts xc
                  where personal2_id is not null
                  ) gr
          group by gr.id,
                gr.contract_date,
                gr.contract_d,
                gr.contractat_flg,
                gr.contract_no,
                gr.ref_company_fk,
                gr.company,
                gr.finalizat_flg,
                gr.finalizat,
                gr.amount,
                gr.prepayment,
                gr.datorie,
                gr.uid,
                gr.uname
) un
LEFT OUTER JOIN  contract_payments cp ON (un.id = cp.contract_id_fk and cp.personal_fk = un.uid)  
union all
select 
        bn.* , 
        cp.amount as payd_amount, 
        cp.date_trans as payment_date
from (
    select    gr.id,
        gr.contract_date,
        gr.contract_d,
        gr.contractat_flg,
        gr.contract_no,
        gr.ref_company_fk,
        gr.company,
        gr.finalizat_flg,
        gr.finalizat,
        gr.amount,
        gr.prepayment,
        gr.datorie,
        gr.uid,
        gr.uname,
        sum(gr.uamount)
     from (select 
                xr.id,
                xr.contract_date,
                xr.contract_d,
                xr.contractat_flg,
                xr.contract_no,
                xr.ref_company_fk,
                xr.company,
                xr.finalizat_flg,
                xr.finalizat,
                cagr.amount as amount,
                xr.prepayment,
                xr.datorie,
                cagr.partener_fk         as uid,
                "get_reg_parteners_cname_by_id"(cagr.partener_fk) as uname,
                 ROUND(((COALESCE(cagr.amount, 0.00) / (100)::NUMERIC) * get_reg_partners_procent_by_id(cagr.partener_fk)), 2) as  uamount
          from x_contracts xr, contract_add_agree cagr
          where xr.id = cagr.contract_id_fk) gr
          group by  gr.id,
                gr.contract_date,
                gr.contract_d,
                gr.contractat_flg,
                gr.contract_no,
                gr.ref_company_fk,
                gr.company,
                gr.finalizat_flg,
                gr.finalizat,
                gr.amount,
                gr.prepayment,
                gr.datorie,
                gr.uid,
                gr.uname
  ) bn
 LEFT OUTER JOIN  contract_payments cp ON (bn.id = cp.contract_id_fk and cp.partener_fk = bn.uid);
 
 CREATE OR REPLACE VIEW v_contract_payments AS 
select id, contract_no, contract_date, amount, sum, uid, sum(payd_amount) payd_amount from vreport_contract_payments
where contractat_flg=1 or finalizat_flg=1
group by id, contract_no, contract_date, amount, sum, uid;

ALTER TABLE v_contract_payments
  OWNER TO mf;